﻿using JHT.EFMigrationTest.ClassLibrary1;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Migrations;
using System.Linq;
using System.Runtime.Caching;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Z.EntityFramework.Plus;

namespace JHT.EFMigrationTest.ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //TestExplictLoad();
                //TestEFEntenisonBatchDelete();
                //TestEFPlusCacheQuery();
                //TestEFPlusCacheExpirePolicy();
                //TestEFPlusIncludeFilter();
                //TestEFPlustIncludeOptimized();
                //TestEFFind();
                //TestEfCompliedQuery();
                //TestEFLingQueryForIEnumrableContain();
                //TestNonMappedObject();
                //TestQuery();
                //TestEFSqlQuery();
                TestAnyEFQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.ReadLine();
        }

        /// <summary>
        /// 测试查询
        /// </summary>
        private static void TestLazyQuery()
        {
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Configuration.LazyLoadingEnabled = true;
                dbContexnt.Database.Log = (msg) =>
                {
                    Console.WriteLine(msg);
                };
                var result = dbContexnt.Students.FirstOrDefault();
                if (result != null)
                {
                    Console.WriteLine(result.StudentName);
                }
                else
                {
                    Console.WriteLine("测试");
                }

                dbContexnt.SaveChanges();
            }
        }
        /// <summary>
        ///测试使用存储过程
        /// </summary>
        private static void TestStoreProc()
        {
            using (TestDbContext dbContexnt = new TestDbContext())
            {

            }
        }
        /// <summary>
        /// 测试精确查询
        /// </summary>
        private static void TestExplictLoad()
        {
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = (msg) =>
                {
                    Console.WriteLine(msg);
                };
                dbContexnt.Configuration.LazyLoadingEnabled = false;
                var teachers = dbContexnt.TeacherInfos.FirstOrDefault();

                if (teachers != null)
                {
                    // 这里是将加载的数据放到teacher对象中
                    Console.WriteLine(teachers.TeacherName);
                    dbContexnt.Entry(teachers).Collection(p => p.ClassInfos).Load();
                    Console.WriteLine(teachers.ClassInfos.FirstOrDefault().ClassName);
                    // 还可以利用Query来对加载的实体进行条件过率，但是加载的数据为单独的对象
                    var resl = dbContexnt.Entry(teachers).Collection(p => p.ClassInfos).Query().Where(p => p.Id == 1).FirstOrDefault();
                    if (resl != null)
                    {
                        Console.WriteLine(resl.ClassName);
                    }
                }
            }
        }

        #region EPlus查询

        /// <summary>
        /// 测试批量更新
        /// </summary>
        private static void TestEFEntenisonBatchDelete()
        {
            List<Books> books = new List<Books>()
            {
                new Books(){BookAuthor="张鑫",BookName="C#从入门到放弃",BookNo="BSN20180806",Edition=1},
                new Books(){BookAuthor="陈斌",BookName="Java从入门到放弃",BookNo="BSN20180806",Edition=2},
                new Books(){BookAuthor="李智",BookName="Python从入门到放弃",BookNo="BSN20180806",Edition=3},
                new Books(){BookAuthor="杨月志",BookName="Js从入门到放弃",BookNo="BSN20180806",Edition=4},
                new Books(){BookAuthor="尹建明",BookName="GO从入门到放弃",BookNo="BSN20180806",Edition=5},
            };

            using (var dbContext = new TestDbContext())
            {
                Console.WriteLine("EF批量添加");
                dbContext.Database.Log = Console.WriteLine;
                dbContext.Books.AddOrUpdate(p => p.BookName, books.ToArray());

                dbContext.SaveChanges();
                Console.WriteLine("EFPlus条件批量删除");
                dbContext.Books.Where(p => p.BookName == "Python从入门到放弃从入门到放弃").Delete();
                dbContext.SaveChanges();

                Console.WriteLine("EFPlus条件批量更新");
                dbContext.Books.Where(p => p.BookName == "C#从入门到放弃从入门到放弃").Update(p => new Books() { BookNo = "MBSN20180806" });
                dbContext.SaveChanges();
            }
        }

        /// <summary>
        /// 测试EFPlus查询组件
        /// </summary>
        private static void TestEFPlusCacheQuery()
        {
            Console.WriteLine("执行查询缓存功能：");
            var bookName = "";
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = Console.WriteLine;
                Console.WriteLine("第一次执行查询：");
                // 查询缓存
                var list = dbContexnt.Books.FromCache();
                foreach (var item in list)
                {
                    bookName += item.BookName + "\r\n";
                }
                Console.WriteLine(bookName);
            }

            bookName = "";
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                Console.WriteLine("第二次执行查询不再执行数据库操作：");
                dbContexnt.Database.Log = Console.WriteLine;
                // 查询缓存
                var list = dbContexnt.Books.FromCache();
                foreach (var item in list)
                {
                    bookName += item.BookName + "\r\n";
                }
                Console.WriteLine(bookName);
            }
        }

        /// <summary>
        /// 测试缓存标记，和缓存过期处理
        /// </summary>
        private static void TestEFPlusCacheQueryTag()
        {
            Console.WriteLine("执行查询缓存功能Tag及过期处理：");
            var bookName = "";
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = Console.WriteLine;
                var books = dbContexnt.Books.FromCache("Books", "Book");// 建议使用
                foreach (var item in books)
                {
                    bookName += item.BookName + "\r\n";
                }

                Console.WriteLine(bookName);
                Console.WriteLine("使缓存过期:QueryCacheManager.ExpireTag");
                QueryCacheManager.ExpireTag("Books");// 设置缓存过期
                books = dbContexnt.Books.FromCache();
                Console.WriteLine(books.Count());
            }
        }

        /// <summary>
        /// 设置缓存过期策略
        /// </summary>
        private static void TestEFPlusCacheExpirePolicy()
        {
            Console.WriteLine("执行查询缓存功能设置缓存过期策略");
            var bookName = "";
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = Console.WriteLine;
                var cachePolicy = new CacheItemPolicy()
                {
                    AbsoluteExpiration = DateTime.Now.AddSeconds(10),// 设置绝对过期
                    RemovedCallback = p => Console.WriteLine("缓存已过期"),// 设置过期回调

                };
                var books = dbContexnt.Books.AsNoTracking().FromCache(cachePolicy, "books");// 建议使用
                foreach (var item in books)
                {
                    bookName += item.BookName + "\r\n";
                }
                QueryCacheManager.ExpireTag("books");// 手动设置缓存过期
                Console.WriteLine(bookName);
                //Thread.Sleep(1000 * 20);
                Console.WriteLine("从数据库中执行查询");
                bookName = "";
                books = dbContexnt.Books.FromCache();
                foreach (var item in books)
                {
                    bookName += item.BookName + "\r\n";
                }
                Console.WriteLine(bookName);

                // 全局设置cache的过期策略
                QueryCacheManager.Cache = MemoryCache.Default;
                var options = new CacheItemPolicy() { SlidingExpiration = TimeSpan.FromHours(2) };//设置滑动过期
                options.ChangeMonitors.Add(new HostFileChangeMonitor(new List<string>() { @"C:\cache.config" }));
                QueryCacheManager.DefaultCacheItemPolicy = options;
            }
        }

        /// <summary>
        /// 测试关联查询筛查 
        /// 只针对1对多的情况
        /// </summary>
        private static void TestEFPlusIncludeFilter()
        {
            Console.WriteLine("执行关联查询筛选");
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                //使用属性关联查询需要，引入命名空间： System.Data.Entity
                //dbContexnt.TeacherInfos.Include(path => path.ClassInfos);

                dbContexnt.Database.Log = Console.WriteLine;
                dbContexnt.Configuration.LazyLoadingEnabled = false;
                var classInfos = dbContexnt.ClassInfos.IncludeFilter(p => p.Students.Where(q => q.StudentName == "zx"));  // 这个查询不会加载关联的班级信息
                foreach (var item in classInfos)
                {
                    var classNames = item.Students != null && item.Students.Count > 0 ? string.Join(",", item.Students.Select(p => p.StudentName)) : "";
                    Console.WriteLine("班级名称：" + item.ClassName + " \r\n 关联学生：" + classNames + "\r\n ");
                }
            }
        }

        /// <summary>
        /// 测试关联查询优化方法
        /// 只针对1对多，1对1的情况 这样可以减少查询出来的数据提高查询效率
        /// </summary>
        private static void TestEFPlustIncludeOptimized()
        {
            Console.WriteLine("执行关联查询筛选");
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                //使用属性关联查询需要，引入命名空间： System.Data.Entity
                //dbContexnt.TeacherInfos.Include(path => path.ClassInfos);

                dbContexnt.Database.Log = Console.WriteLine;
                dbContexnt.Configuration.LazyLoadingEnabled = false;
                var teachers = dbContexnt.Students
                    .Where(p => p.StudentName == "zx")
                    .IncludeOptimized(p => p.ClassInfo).ToList();  // 这个查询不会加载关联的班级信息，不支持多对多的查询
                foreach (var item in teachers)
                {
                    var classNames = item.ClassInfo != null ? string.Join(",", item.ClassInfo.ClassName) : "";
                    Console.WriteLine("学生姓名：" + item.StudentName + " 关联班级：" + classNames + "\r\n ");
                }
            }
        }
        #endregion

        #region 查询优化

        private static void TestEFQuery()
        {
            using (var dbContext = new TestDbContext())
            {
                var objQuery = dbContext.Books.AsQueryable() as ObjectQuery;
                objQuery.EnablePlanCaching = true;//是否执行查询计划缓存
            }
        }

        /// <summary>
        /// Find方法 先查询缓存，再查询数据库，如果缓存中的数据很多，会导致查询效率变低
        /// 因为查询会触发对高速缓存的对象的验证
        ///  context.Configuration.AutoDetectChangesEnabled=false  可通过此操作改变效率
        /// </summary>
        private static void TestEFFind()
        {
            using (var dbContext = new TestDbContext())
            {
                Console.WriteLine("测试EFFind方法，先查询缓存，再查询数据库");
                dbContext.Database.Log = Console.WriteLine;
                var edition = 1;
                var books = dbContext.Books.Where(p => p.Edition > edition).ToList();
                Console.WriteLine("加载部分书籍名称");
                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }

                var book = dbContext.Books.Find("BSN20180806", 2);
                Console.WriteLine("从缓存中获取，而不用查找数据库");
                if (book != null)
                {
                    Console.WriteLine(book.BookName);
                }

                Console.WriteLine("从缓存中获取不到，从数据库中查询");
                book = dbContext.Books.Find("BSN20180806", 1);
                if (book != null)
                {
                    Console.WriteLine(book.BookName);
                }

                Console.WriteLine("查询未保存到数据库中的对象");
                dbContext.Books.Add(new Books() { BookName = "C#新增", BookAuthor = "扎实", BookNo = "BSN20180806", Edition = 6 });
                book = dbContext.Books.Find("BSN20180806", 6);
                if (book != null)
                {
                    Console.WriteLine("返回未保存的对象信息");
                    Console.WriteLine(book.BookName);
                }
            }
        }

        /// <summary>
        /// 分页的参数化查询
        /// </summary>
        private static void TestEfCompliedQuery()
        {
            using (var dbContext = new TestDbContext())
            {
                Console.WriteLine("EF 分页查询是否为参数话的查询");
                dbContext.Database.Log = Console.WriteLine;
                var endIndex = 10;
                var startIndex = 0;
                var books = dbContext.Books.Where(p => p.Edition > 0).OrderBy(p => p.BookName).Take(() => endIndex).Skip(() => startIndex);// 会执行参数化查询，缓存计划会被缓存
                //var books = dbContext.Books.Where(p => p.Edition > 0).OrderBy(p => p.BookName).Take( endIndex).Skip( startIndex);// 不会执行参数化查询，一旦输入值变了就会刷新缓存计划
                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }
            }
        }


        private static void TestEFLingQueryForIEnumrableContain()
        {
            using (var dbContext = new TestDbContext())
            {
                Console.WriteLine("EF 分页查询是否为参数话的查询");
                dbContext.Database.Log = Console.WriteLine;
                int[] editon = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 172, 20 };
                var books = dbContext.Books.Where(p => editon.Contains(p.Edition));// 尽量避免使用此种查询，因为查询计划不会被缓存，每次都会实时编译，编译的快慢取决于数组大小
                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }
            }
        }

        private static void TestNonMappedObject()
        {
            using (var dbContext = new TestDbContext())
            {
                Test test = new Test() { name = "C" };
                Console.WriteLine("EF 非实体对象的查询");
                dbContext.Database.Log = Console.WriteLine;
                var books = dbContext.Books.Where(p => p.BookName.StartsWith(test.name));// 尽量避免使用此种查询，因为查询计划不会被缓存，每次都会实时编译
                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }
                var name = test.name;
                books = dbContext.Books.Where(p => p.BookName.StartsWith(name));// 尽使用此种查询，查询计划会被缓存
                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }
            }
        }


        private static void TestQuery()
        {
            using (var dbContext = new TestDbContext())
            {
                Test test = new Test() { name = "C" };
                Console.WriteLine("EF 非实体对象的查询");
                dbContext.Database.Log = Console.WriteLine;
                var books = dbContext.Books.Select(p => new
                {
                    BookName = p.BookName
                }).ToList();// 这不会产生实体状态跟踪

                foreach (var item in books)
                {
                    Console.WriteLine(item.BookName);
                }
            }
        }

        #endregion

        #region EF 查询优化

        public static void TestEFSqlQuery()
        {
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = (msg) =>
                {
                    Console.WriteLine(msg);
                };

                Console.WriteLine("不会进行状态跟踪");
                var bookResult = dbContexnt.Database.SqlQuery<Books>("select * from Books");
                foreach (var item in bookResult)
                {
                    Console.WriteLine(item.BookName);
                }
                var firstBook = bookResult.FirstOrDefault();
                firstBook.BookName = "Modify" + firstBook.BookName;
                dbContexnt.SaveChanges();
                Console.WriteLine("没有生成修改语句");
            }
        }

        public static void TestAnyEFQuery()
        {
            using (TestDbContext dbContexnt = new TestDbContext())
            {
                dbContexnt.Database.Log = (msg) =>
                {
                    Console.WriteLine(msg);
                };


                var result = dbContexnt.Books.Count(p => p.BookName.StartsWith("C#")) > 0;
                Console.WriteLine("FirstOrDefault是否存在：" + result);
                result = dbContexnt.Books.Any(p => p.BookName.StartsWith("C#"));
                Console.WriteLine("Any是否存在：" + result);
                result = dbContexnt.Books.FirstOrDefault(p => p.BookName.StartsWith("C#")) != null;
                Console.WriteLine("Count是否存在：" + result);
                Console.WriteLine("没有生成修改语句");
            }
        }
        #endregion
    }


    public class NewBook
    {
        public string BookName { get; set; }
    }

    public class Test
    {
        public string name { get; set; }
    }
}
